﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Runtime.InteropServices;

namespace ParkService
{
    /// <summary>
    /// SQL Server 数据库的操作类库。代码原创。
    /// </summary>
    public class SqlServerHelper
    {
        #region[字段]
        private string connectstring = "Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;Integrated Security=True";
        SqlConnection connect = null;
        SqlCommand command = null;
        private int CommandTimeOut = 30;
        #endregion

        #region[属性]
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectString
        {
            get { return connectstring; }
            set { connectstring = value; }
        }
        #endregion

        /// <summary>
        /// 改变数据库的连接参数
        /// </summary>
        /// <param name="connectString"></param>
        public void ChangeConnect(string connectString)
        {
            this.ConnectString = connectString;
            connect = new SqlConnection(ConnectString);
        }

        #region[构造函数]
        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="ConStr">正确的数据库连接字符串</param>
        /// <param name="TimeOut">超时时间</param>
        public SqlServerHelper(string ConStr, int TimeOut)
        {
            ConnectString = ConStr;
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = TimeOut;
        }
        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="ConStr">正确的数据库连接字符串</param>
        public SqlServerHelper(string ConStr)
        {
            ConnectString = ConStr;
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = 15;
        }
        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="DataServer">数据库服务器名称或地址</param>
        /// <param name="DataBase">访问的数据库</param>
        /// <param name="UserID">用户名</param>
        /// <param name="Pwd">密码</param>
        /// <param name="TimeOut">超时时间</param>
        public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd, int TimeOut)
        {
            ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd);
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = TimeOut;
        }
        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="DataServer">数据库服务器名称或地址</param>
        /// <param name="DataBase">访问的数据库</param>
        /// <param name="UserID">用户名</param>
        /// <param name="Pwd">密码</param>
        public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd)
        {
            ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd);
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = 30;
        }

        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="database">访问的数据库</param>
        /// <param name="isLocal">是否为本地数据库？若False，则抛出异常</param>
        /// <param name="TimeOut">超时时间</param>
        public SqlServerHelper(string database, bool isLocal, int TimeOut)
        {
            if (!isLocal)
            {
                throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。"));
            }
            connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database);
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = TimeOut;
        }
        /// <summary>
        /// 构造函数，初始化
        /// </summary>
        /// <param name="database">访问的数据库</param>
        /// <param name="isLocal">是否为本地数据库？若False，则抛出异常</param>
        public SqlServerHelper(string database, bool isLocal)
        {
            if (!isLocal)
            {
                throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。"));
            }
            connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database);
            connect = new SqlConnection(ConnectString);
            CommandTimeOut = 30;
        }
        #endregion

        #region[私有函数]
        private void Open()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Open)
                {
                    connect.Open();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }

        private void Close()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Closed)
                {
                    connect.Close();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }
        #endregion

        /// <summary>
        /// 测试是否能够连通
        /// </summary>
        /// <returns>布尔值</returns>
        public bool ConnectTest()
        {
            bool return_flag = true;
            try
            {
                connect.Open();
            }
            catch
            {
                return_flag = false;
            }
            finally
            {
                connect.Close();
            }
            return return_flag;
        }

        /// <summary>
        /// 执行无返回的Sql语句，如插入，删除，更新
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <returns>受影响的条数</returns>
        public int ExecuteNonQuery(string sqlstr)
        {
            try
            {
                Open();
                command = new SqlCommand(sqlstr, connect);
                int num = command.ExecuteNonQuery();
                command.Parameters.Clear();
                Close();
                return num;
            }
            catch
            {
                throw;
            }
            finally
            {
                connect.Close();
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns>DataSet数据集</returns>
        public DataSet ReturnDataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            try
            {
                Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connect);
                adapter.Fill(ds, "Obj");
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connect.Close();
            }
            return ds;
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sqlstr">Sqk</param>
        /// <returns>DataTable数据表</returns>
        public DataTable ReturnDataTable(string sqlstr)
        {
            return ReturnDataSet(sqlstr).Tables[0];
        }

        /// <summary>
        /// 执行查询语句，返回DataReader
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ReturnDataReader(string sqlstr)
        {

            try
            {
                Open();
                command = new SqlCommand(sqlstr, connect);
                SqlDataReader myReader = command.ExecuteReader();
                command.Parameters.Clear();
                Close();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {

            Open();
            command = new SqlCommand();
            command.Connection = connect;
            SqlTransaction tx = connect.BeginTransaction();
            command.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        command.CommandText = strsql;
                        command.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (Exception)
            {
                tx.Rollback();
                throw;
            }
        }

        #region 供使用API方式时使用
        [DllImport("odbc32.dll")]
        private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
        [DllImport("odbc32.dll")]
        private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
        [DllImport("odbc32.dll")]
        private static extern short SQLFreeHandle(short hType, IntPtr handle);
        [DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)]
        private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
            short inStringLength, System.Text.StringBuilder outString, short outStringLength,
            out short outLengthNeeded);

        private const short SQL_HANDLE_ENV = 1;
        private const short SQL_HANDLE_DBC = 2;
        private const int SQL_ATTR_ODBC_VERSION = 200;
        private const int SQL_OV_ODBC3 = 3;
        private const short SQL_SUCCESS = 0;
        private const short SQL_NEED_DATA = 99;
        private const short DEFAULT_RESULT_SIZE = 1024;

        private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
        #endregion
        /// <summary>
        /// 获取网内的数据库服务器名称(API方式)
        /// </summary>
        /// <returns>服务器名称数组</returns>
        public static string[] GetServers()
        {
            string list = string.Empty;
            IntPtr henv = IntPtr.Zero;
            IntPtr hconn = IntPtr.Zero;
            System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
            System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
            short inStringLength = (short)inString.Length;
            short lenNeeded = 0;
            try
            {
                if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
                {
                    if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
                    {
                        if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
                        {

                            if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                 DEFAULT_RESULT_SIZE, out lenNeeded))
                            {
                                if (DEFAULT_RESULT_SIZE < lenNeeded)
                                {
                                    outString.Capacity = lenNeeded;
                                    if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                         lenNeeded, out lenNeeded))
                                    {
                                        throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
                                    }
                                }
                                list = outString.ToString();
                                int start = list.IndexOf("{") + 1;
                                int len = list.IndexOf("}") - start;
                                if ((start > 0) && (len > 0))
                                {
                                    list = list.Substring(start, len);
                                }
                                else
                                {
                                    list = string.Empty;
                                }
                            }
                        }
                    }
                }
            }
            catch
            {
                list = string.Empty;
            }

            finally
            {
                if (hconn != IntPtr.Zero)
                {
                    SQLFreeHandle(SQL_HANDLE_DBC, hconn);
                }

                if (henv != IntPtr.Zero)
                {
                    SQLFreeHandle(SQL_HANDLE_ENV, hconn);
                }
            }

            string[] array = null;

            if (list.Length > 0)
            {

                array = list.Split(',');

            }
            return array;
        }

        /// <summary>
        /// 获取网内的数据库服务器名称(qlClientFactory方式)
        /// </summary>
        /// <returns>服务器名称数组</returns>
        public static string[] GetServersBySqlClientFactory()
        {
            DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
            DataColumn column2 = dataSources.Columns["ServerName"];
            DataColumn column = dataSources.Columns["InstanceName"];
            DataRowCollection rows = dataSources.Rows;
            string[] array = new string[rows.Count];
            for (int i = 0; i < array.Length; i++)
            {
                string str2 = rows[i][column2] as string;
                string str = rows[i][column] as string;
                if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str))
                {
                    array[i] = str2;
                }
                else
                {
                    array[i] = str2 + @"\" + str;
                }
            }
            Array.Sort<string>(array);

            return array;
        }

        /// <summary>
        /// 根据不带数据库的连接字符串，遍历查找出所有数据库实例
        /// </summary>
        /// <returns>指定服务器的所有数据库</returns>
        public string[] GetDataBases()
        {
            List<string> list = new List<string>();
            SqlConnection sqlConnection = new SqlConnection(ConnectString);
            string[] result;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand("select  name AS 数据库 from master..sysdatabases", sqlConnection);
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    list.Add(sqlDataReader.GetString(0));
                }
                sqlDataReader.Close();
                string[] array = new string[]
                {
                    "master",
                    "tempdb",
                    "model",
                    "msdb"
                };
                string[] array2 = array;
                for (int i = 0; i < array2.Length; i++)
                {
                    string item = array2[i];
                    try
                    {
                        list.Remove(item);
                    }
                    catch
                    {
                    }
                }
                result = list.ToArray();
            }
            catch
            {
                result = list.ToArray();
            }
            finally
            {
                sqlConnection.Close();
            }
            return result;
        }

        /// <summary>
        /// 获取SqlServer指定数据库的所有表
        /// </summary>
        /// <returns>表集合,出错则产生异常</returns>
        public string[] GetTables()
        {
            string sql = "select object_name (id) from sysobjects where xtype = 'u' and objectproperty (id,'IsMSShipped') = 0";
            DataTable dt = ReturnDataTable(sql);
            List<string> Ls = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Ls.Add(dt.Rows[i][0].ToString());
            }
            return Ls.ToArray();
        }

        /// <summary>
        /// 获取指定表的所有列
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <returns>列集合,出错则产生异常</returns>
        public string[] GetColumns(string TableName)
        {
            string sql = string.Format("select name from syscolumns where id=object_id('{0}')", TableName);
            try
            {
                List<string> Ls = new List<string>();
                DataTable dt = ReturnDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Ls.Add(dt.Rows[i][0].ToString());
                }
                return Ls.ToArray();
            }
            catch
            {

                throw;
            }
        }
    }
}
